#!/usr/bin/python2.4
#
# Copyright 2011 Google Inc. All Rights Reserved.

from BeautifulSoup import BeautifulSoup as BS
from BeautifulSoup import Tag

from controllers import geocode, ftclient
import datetime
import logging
import urllib
import re

from google.appengine.ext import db
from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app

import csv

TABLE = 401993
FLOOD_DATA = 'http://livetraffic.rta.nsw.gov.au/traffic/hazards/flood.json'
#INCIDENT_DATA = 'http://livetraffic.rta.nsw.gov.au/traffic/hazards/incident.json'

COLUMNS = ['geolocation', 'source', 'name', 'ismajor', 'ended', 'category', 'description', 'urlname', 'urllink']

# the FT api calls fail unless with escape the HTML
def escape(html):
    return html.replace('&', '&amp;').replace('"', '&quot;').replace("'", '&#39;').replace('\/', '/')

# these allow us to eval() the JSON result
true = True
false = False

class FusionTableUser(db.Model):
  user = db.StringProperty(required=True)
  passwd = db.StringProperty(required=True)

def getLatLng(data):
  if data is not None and ('type' in data) and (data['type'] == 'POINT'):
    return (data['coordinates'][1], data['coordinates'][0])
  else:
    return None

def getData(URL):
  result = []

  json = eval(urllib.urlopen(URL).read())

  if not ('layerName' in json) or not ('features' in json):
    return None

  source = json['layerName']
  for feature in json['features']:
    if not ('type' in feature) or feature['type'] != 'Feature':
      continue
 
    id = feature['id']
    location = getLatLng(feature['geometry'])
    if location is None:
      return

    (lat,lng) = location

    props = feature['properties']

    # given we have only the one location, I am only going to use the first piece of road information
    if len(props['roads']) < 1:
      continue
    road = props['roads'][0]

    result.append({
      'id': id,
      'source': source,
      'geolocation': str(lat) + ',' + str(lng),
      'name': props['headline'],
      'ismajor': str(props['isMajor']),
      'ended': str(props['ended']), 
      'category': props['mainCategory'],
      'description': escape(props['otherAdvice']),
      'urlname' : props['webLinkName'],
      'urllink': props['webLinkUrl']
    })
  
  return result

def loadData(): 
  data = []
  for URL in [ FLOOD_DATA ]:
    for result in getData(URL):
      data.append(result)

  updateTable(TABLE, COLUMNS, data)

def updateTable(table, columns, entries):
  
  password = FusionTableUser.all().filter("user = ", "vicroadclosures@gmail.com").get().passwd
  #password = open("password-vic.txt").read().strip()
  
  authtoken = ftclient.GoogleClientLogin('vicroadclosures@gmail.com',password)

  ft = ftclient.FTClient(authtoken)

  query = "select rowid, %s from %s" % (', '.join(columns), table)
  print query
  out = ft.runGetQuery(query)

  existing = {}

  for line in csv.reader(out.split('\n')):
    if not line: continue
    exist = [x.decode('utf-8') for x in line[1:]]

    if line[0] == 'rowid': continue
    existing[line[0]] = exist
  new = []
  for line in entries:
    e = []
    for c in columns: e.append(line[c])
    new.append(e)

  to_remove = []
  to_add = []
  to_keep = []
  print "existing: %s new: %s"%(len(existing), len(new))

  for rowid, entry in existing.items():
    if entry in new:
      # keep
      to_keep.append(entry)
    else:
      to_remove.append(rowid)

  for entry in new:
    if entry in existing.values():
      # keep
      pass
    else:
      to_add.append(entry)

  print "remove: %s add: %s keep: %s" %(len(to_remove), len(to_add),
                                        len(to_keep))

  for rowid in to_remove:
    query = "delete from %s where rowid = '%s'"%(table, rowid)
    print query
    ft.runGetQuery(query)

  column_s = ', '.join(columns)
  for data in to_add:
    values = [x.replace("'", "\\'") for x in data]
    values = ', '.join("'%s'"%x for x in values)

    query = "insert into %s (%s) VALUES (%s)"%(table, column_s, values)
    print query
    ft.runGetQuery(query)

  # Once off to add a new icon.
  #for data in to_keep:
  #  query = "update %s SET icon = 'placemark_circle_highlight' WHERE rowid =
  #  '%s'" % (table, data[1])
  #  print query 
  #  ft.runGetQuery(query)
  
# TODO(arb):
# Hook up handler
# Hook up a cache thingy that rewrites the icon (ask bst)

class UpdatePage(webapp.RequestHandler):
  def get(self):
    loadData()
    self.response.headers['Content-Type'] = 'text/plain'
    self.response.out.write('OK')

application = webapp.WSGIApplication([('/nswrta/update__', UpdatePage),
                                      ], debug=True)

def main():
  run_wsgi_app(application)

if __name__ == "__main__":
  loadData()
